﻿using Dapper;
using Microsoft.AspNetCore.Mvc;
using Microsoft.SqlServer.Server;
using System.Data;
using System.Data.SqlClient;
using Xm.Entity;
using static System.Runtime.InteropServices.JavaScript.JSType;

namespace Xm.Models
{
    public class TestLogin : ITestLogin
    {
        
        string connectionString = "server=.;database=HR_DB;uid=sa;pwd=123456;";
        public async Task<int> UpdateInterviewAsync(engage_resume model)
        {
            using (SqlConnection con = new SqlConnection(connectionString))
            {
                var parameters = new DynamicParameters();

                // 添加所有需要更新的参数
                parameters.Add("@res_id", model.res_id);
                parameters.Add("@human_name", model.human_name);
                parameters.Add("@engage_type", model.engage_type);
                parameters.Add("@human_address", model.human_address);
                parameters.Add("@human_postcode", model.human_postcode);
                parameters.Add("@human_major_kind_id", model.human_major_kind_id);
                parameters.Add("@human_major_kind_name", model.human_major_kind_name);
                parameters.Add("@human_major_id", model.human_major_id);
                parameters.Add("@human_major_name", model.human_major_name);
                parameters.Add("@human_telephone", model.human_telephone);
                parameters.Add("@human_homephone", model.human_homephone);
                parameters.Add("@human_mobilephone", model.human_mobilephone);
                parameters.Add("@human_email", model.human_email);
                parameters.Add("@human_hobby", model.human_hobby);
                parameters.Add("@human_specility", model.human_specility);
                parameters.Add("@human_sex", model.human_sex);
                parameters.Add("@human_religion", model.human_religion);
                parameters.Add("@human_party", model.human_party);
                parameters.Add("@human_nationality", model.human_nationality);
                parameters.Add("@human_race", model.human_race);
                parameters.Add("@human_birthday", model.human_birthday);
                parameters.Add("@human_age", model.human_age);
                parameters.Add("@human_educated_degree", model.human_educated_degree);
                parameters.Add("@human_educated_years", model.human_educated_years);
                parameters.Add("@human_educated_major", model.human_educated_major);
                parameters.Add("@human_college", model.human_college);
                parameters.Add("@human_idcard", model.human_idcard);
                parameters.Add("@human_birthplace", model.human_birthplace);
                parameters.Add("@demand_salary_standard", model.demand_salary_standard);
                parameters.Add("@human_history_records", model.human_history_records);
                parameters.Add("@remark", model.remark);
               
                parameters.Add("@human_picture", model.human_picture);
                parameters.Add("@attachment_name", model.attachment_name);
                parameters.Add("@check_status", model.check_status);
                parameters.Add("@register", model.register);
                parameters.Add("@regist_time", model.regist_time);
                parameters.Add("@checker", model.checker);
                parameters.Add("@check_time", model.check_time);
                parameters.Add("@total_points", model.total_points);
                parameters.Add("@test_amount", model.test_amount);
                parameters.Add("@test_checker", model.test_checker);
                parameters.Add("@test_check_time", model.test_check_time);
                parameters.Add("@pass_register", model.pass_register);
                parameters.Add("@pass_regist_time", model.pass_regist_time);
                parameters.Add("@pass_checker", model.pass_checker);
                parameters.Add("@pass_check_time", model.pass_check_time);
                parameters.Add("@pass_check_status", model.pass_check_status);
                parameters.Add("@pass_checkComment", model.pass_checkComment);
                parameters.Add("@pass_passComment", model.pass_passComment);

                // 构建UPDATE SQL语句
                string sql = @"
UPDATE engage_resume
SET 
    human_name = @human_name,
    engage_type = @engage_type,
    human_address = @human_address,
    human_postcode = @human_postcode,
    human_major_kind_id = @human_major_kind_id,
    human_major_kind_name = @human_major_kind_name,
    human_major_id = @human_major_id,
    human_major_name = @human_major_name,
    human_telephone = @human_telephone,
    human_homephone = @human_homephone,
    human_mobilephone = @human_mobilephone,
    human_email = @human_email,
    human_hobby = @human_hobby,
    human_specility = @human_specility,
    human_sex = @human_sex,
    human_religion = @human_religion,
    human_party = @human_party,
    human_nationality = @human_nationality,
    human_race = @human_race,
    human_birthday = @human_birthday,
    human_age = @human_age,
    human_educated_degree = @human_educated_degree,
    human_educated_years = @human_educated_years,
    human_educated_major = @human_educated_major,
    human_college = @human_college,
    human_idcard = @human_idcard,
    human_birthplace = @human_birthplace,
    demand_salary_standard = @demand_salary_standard,
    human_history_records = @human_history_records,
    remark = @remark,
   
    human_picture = @human_picture,
    attachment_name = @attachment_name,
    check_status = @check_status,
    register = @register,
    regist_time = @regist_time,
    checker = @checker,
    check_time = @check_time,
   
    total_points = @total_points,
    test_amount = @test_amount,
    test_checker = @test_checker,
    test_check_time = @test_check_time,
    pass_register = @pass_register,
    pass_regist_time = @pass_regist_time,
    pass_checker = @pass_checker,
    pass_check_time = @pass_check_time,
    pass_check_status = @pass_check_status,
    pass_checkComment = @pass_checkComment,
    pass_passComment = @pass_passComment
WHERE res_id = @res_id;";

                return await con.ExecuteAsync(sql, parameters);
            }
        }
        public async Task<List<InterviewResumeViewModel>> cx()
        {

            using (SqlConnection con = new SqlConnection("server=.;database=HR_DB;uid=sa;pwd=123456"))
            {
                string sql = @$"select * from dbo.engage_interview a,dbo.engage_resume b where a.human_name=b.human_name";
                return (await con.QueryAsync<InterviewResumeViewModel>(sql)).ToList();
            }

        }
        public async Task<int> InsertInterviewAsync(engage_interview model)
        {
            using (SqlConnection con = new SqlConnection(connectionString))
            {
                var parameters = new DynamicParameters();
                
                parameters.Add("@human_name", model.human_name);
                parameters.Add("@interview_amount", model.interview_amount);
                parameters.Add("@human_major_kind_id", model.human_major_kind_id);
                parameters.Add("@human_major_kind_name", model.human_major_kind_name);
                parameters.Add("@human_major_id", model.human_major_id);
                parameters.Add("@human_major_name", model.human_major_name);
                parameters.Add("@image_degree", model.image_degree);
                parameters.Add("@native_language_degree", model.native_language_degree);
                parameters.Add("@foreign_language_degree", model.foreign_language_degree);
                parameters.Add("@response_speed_degree", model.response_speed_degree);
                parameters.Add("@EQ_degree", model.EQ_degree);
                parameters.Add("@IQ_degree", model.IQ_degree);
                parameters.Add("@multi_quality_degree", model.multi_quality_degree);
                parameters.Add("@register", model.register);
                parameters.Add("@checker", model.checker);
                parameters.Add("@registe_time", model.registe_time);
                parameters.Add("@check_time", model.check_time);
                parameters.Add("@resume_id", model.resume_id);
                parameters.Add("@result", model.result);
                parameters.Add("@interview_comment", model.interview_comment);
                parameters.Add("@check_comment", model.check_comment);
                parameters.Add("@interview_status", model.interview_status);
                parameters.Add("@check_status", model.check_status);

                string sql = @"
INSERT INTO engage_interview(
    human_name, 
    interview_amount, 
    human_major_kind_id, 
    human_major_kind_name, 
    human_major_id, 
    human_major_name, 
    image_degree, 
    native_language_degree, 
    foreign_language_degree, 
    response_speed_degree, 
    EQ_degree, 
    IQ_degree, 
    multi_quality_degree, 
    register, 
    checker, 
    registe_time, 
    check_time, 
    resume_id, 
    result, 
    interview_comment, 
    check_comment, 
    interview_status, 
    check_status
) VALUES (
    @human_name, 
    @interview_amount, 
    @human_major_kind_id, 
    @human_major_kind_name, 
    @human_major_id, 
    @human_major_name, 
    @image_degree, 
    @native_language_degree, 
    @foreign_language_degree, 
    @response_speed_degree, 
    @EQ_degree, 
    @IQ_degree, 
    @multi_quality_degree, 
    @register, 
    @checker, 
    @registe_time, 
    @check_time, 
    @resume_id, 
    @result, 
    @interview_comment, 
    @check_comment, 
    @interview_status, 
    @check_status
);";

                return await con.ExecuteAsync(sql, parameters);
            }
        }
        public async Task<int> Updateengageresume1(engage_resume model)
        {
                using (SqlConnection con = new SqlConnection(connectionString))
                {
                    await con.OpenAsync();

                    var parameters = new DynamicParameters();

                    // 添加所有需要更新的参数
                    parameters.Add("@res_id", model.res_id);
                    parameters.Add("@human_name", model.human_name);
                    parameters.Add("@engage_type", model.engage_type);
                    parameters.Add("@human_address", model.human_address);
                    parameters.Add("@human_postcode", model.human_postcode);
                    parameters.Add("@human_major_kind_id", model.human_major_kind_id?.Trim());
                    parameters.Add("@human_major_kind_name", model.human_major_kind_name);
                    parameters.Add("@human_major_id", model.human_major_id?.Trim());
                    parameters.Add("@human_major_name", model.human_major_name);
                    parameters.Add("@human_telephone", model.human_telephone);
                    parameters.Add("@human_homephone", model.human_homephone);
                    parameters.Add("@human_mobilephone", model.human_mobilephone);
                    parameters.Add("@human_email", model.human_email);
                    parameters.Add("@human_hobby", model.human_hobby);
                    parameters.Add("@human_specility", model.human_specility);
                    parameters.Add("@human_sex", model.human_sex);
                    parameters.Add("@human_religion", model.human_religion);
                    parameters.Add("@human_party", model.human_party);
                    parameters.Add("@human_nationality", model.human_nationality);
                    parameters.Add("@human_race", model.human_race);
                    parameters.Add("@human_birthday", model.human_birthday);
                    parameters.Add("@human_age", model.human_age);
                    parameters.Add("@human_educated_degree", model.human_educated_degree);
                    parameters.Add("@human_educated_years", model.human_educated_years);
                    parameters.Add("@human_educated_major", model.human_educated_major);
                    parameters.Add("@human_college", model.human_college);
                    parameters.Add("@human_idcard", model.human_idcard);
                    parameters.Add("@human_birthplace", model.human_birthplace);
                    parameters.Add("@demand_salary_standard", model.demand_salary_standard);
                    parameters.Add("@human_history_records", model.human_history_records);
                    parameters.Add("@remark", model.remark);
                    parameters.Add("@recomandation", model.recomandation);
                    parameters.Add("@human_picture", model.human_picture);
                    parameters.Add("@attachment_name", model.attachment_name);
                    parameters.Add("@check_status", model.check_status);
                    parameters.Add("@register", model.register);
                    parameters.Add("@regist_time", model.regist_time);
                    parameters.Add("@checker", model.checker);
                    parameters.Add("@check_time", model.check_time);
                    //parameters.Add("@interview_status", model.interview_status);
                    parameters.Add("@total_points", model.total_points);
                    parameters.Add("@test_amount", model.test_amount);
                    parameters.Add("@test_checker", model.test_checker);
                    parameters.Add("@test_check_time", model.test_check_time);
                    parameters.Add("@pass_register", model.pass_register);
                    parameters.Add("@pass_regist_time", model.pass_regist_time);
                    parameters.Add("@pass_checker", model.pass_checker);
                    parameters.Add("@pass_check_time", model.pass_check_time);
                    parameters.Add("@pass_check_status", model.pass_check_status);
                    parameters.Add("@pass_checkComment", model.pass_checkComment);
                    parameters.Add("@pass_passComment", model.pass_passComment);

                    string sql = @"
                UPDATE engage_resume
                SET 
                    human_name = @human_name,
                    engage_type = @engage_type,
                    human_address = @human_address,
                    human_postcode = @human_postcode,
                    human_major_kind_id = @human_major_kind_id,
                    human_major_kind_name = @human_major_kind_name,
                    human_major_id = @human_major_id,
                    human_major_name = @human_major_name,
                    human_telephone = @human_telephone,
                    human_homephone = @human_homephone,
                    human_mobilephone = @human_mobilephone,
                    human_email = @human_email,
                    human_hobby = @human_hobby,
                    human_specility = @human_specility,
                    human_sex = @human_sex,
                    human_religion = @human_religion,
                    human_party = @human_party,
                    human_nationality = @human_nationality,
                    human_race = @human_race,
                    human_birthday = @human_birthday,
                    human_age = @human_age,
                    human_educated_degree = @human_educated_degree,
                    human_educated_years = @human_educated_years,
                    human_educated_major = @human_educated_major,
                    human_college = @human_college,
                    human_idcard = @human_idcard,
                    human_birthplace = @human_birthplace,
                    demand_salary_standard = @demand_salary_standard,
                    human_history_records = @human_history_records,
                    remark = @remark,
                    recomandation = @recomandation,
                    human_picture = @human_picture,
                    attachment_name = @attachment_name,
                    check_status = @check_status,
                    register = @register,
                    regist_time = @regist_time,
                    checker = @checker,
                    check_time = @check_time,
                    total_points = @total_points,
                    test_amount = @test_amount,
                    test_checker = @test_checker,
                    test_check_time = @test_check_time,
                    pass_register = @pass_register,
                    pass_regist_time = @pass_regist_time,
                    pass_checker = @pass_checker,
                    pass_check_time = @pass_check_time,
                    pass_check_status = @pass_check_status,
                    pass_checkComment = @pass_checkComment,
                    pass_passComment = @pass_passComment
                WHERE 
                    res_id = @res_id";

                    return await con.ExecuteAsync(sql, parameters);


                }
            }
        public async Task<List<engage_resume>> SearchResume(
    string humanMajorKindName,
    string humanMajorName,
    string humanName,
    DateTime? startDate,
    DateTime? endDate)
        {
            string sql = @"
        SELECT *
        FROM dbo.engage_resume
        WHERE 
            (@humanMajorKindName IS NULL OR human_major_kind_name LIKE '%' + @humanMajorKindName + '%')
            AND (@humanMajorName IS NULL OR human_major_name LIKE '%' + @humanMajorName + '%')
            AND (@humanName IS NULL OR human_name LIKE '%' + @humanName + '%')
            AND (
                (@startDate IS NULL AND @endDate IS NULL)
                OR regist_time BETWEEN @startDate AND @endDate
            )
        ORDER BY regist_time DESC;
    ";

            using (var connection = new SqlConnection(connectionString))
            {
                await connection.OpenAsync();

                var parameters = new
                {
                    humanMajorKindName = string.IsNullOrEmpty(humanMajorKindName) ? null : humanMajorKindName,
                    humanMajorName = string.IsNullOrEmpty(humanMajorName) ? null : humanMajorName,
                    humanName = string.IsNullOrEmpty(humanName) ? null : humanName,
                    startDate,
                    endDate
                };

                return (await connection.QueryAsync<engage_resume>(sql, parameters)).ToList();
            }
        }
        public async Task<List<JobCascaderViewModel>> GetJobCascaderDataAsync1()
        {
            try
            {
                using (SqlConnection sqlConnection = new SqlConnection(connectionString))
                {
                    await sqlConnection.OpenAsync();

                    // 查询三级机构数据，使用参数化查询防止SQL注入
                    string sql = @"
                        SELECT 
                            first_kind_id,
                            first_kind_name,
                            second_kind_id,
                            second_kind_name,
                            third_kind_id,
                            third_kind_name 
                        FROM 
                            config_file_third_kind
                        ORDER BY 
                            first_kind_id, second_kind_id, third_kind_id";

                    // 执行查询并映射到实体类
                    var thirdKindData = await sqlConnection.QueryAsync<config_file_third_kind>(sql);

                    // 构建三级级联数据结构
                    var firstLevelCascader = thirdKindData
                        .GroupBy(m => new { m.first_kind_id, m.first_kind_name })
                        .Select(firstGroup => new JobCascaderViewModel
                        {
                            value = firstGroup.Key.first_kind_id,
                            label = firstGroup.Key.first_kind_name,
                            children = firstGroup
                                .GroupBy(m => new { m.second_kind_id, m.second_kind_name })
                                .Select(secondGroup => new JobCascaderViewModel
                                {
                                    value = secondGroup.Key.second_kind_id,
                                    label = secondGroup.Key.second_kind_name,
                                    children = secondGroup
                                        .Select(item => new JobCascaderViewModel
                                        {
                                            value = item.third_kind_id,
                                            label = item.third_kind_name
                                        })
                                        .ToList()
                                })
                                .ToList()
                        })
                        .ToList();

                    return firstLevelCascader;
                }
            }
            catch (SqlException sqlEx)
            {
                // 专门处理SQL异常，记录详细错误信息
                Console.WriteLine($"数据库操作异常 - 获取职位级联数据失败: {sqlEx.Message}");
                Console.WriteLine($"SQL错误代码: {sqlEx.Number}, 状态: {sqlEx.State}");
                throw; // 重新抛出异常或返回空集合
            }
            catch (Exception ex)
            {
                // 处理其他类型异常
                Console.WriteLine($"系统异常 - 获取职位级联{ ex.Message}");
                return new List<JobCascaderViewModel>();
            }
        }


        public async Task<List<JobCascaderViewModel>> GetJobCascaderDataAsync()
        {
            try
            {
                using (SqlConnection sqlConnection = new SqlConnection("server =.; database = HR_DB; uid = sa; pwd = 123456;"))
                {
                    await sqlConnection.OpenAsync();

                    // 查询所有职位数据
                    string sql = @"
                 select 
                     major_kind_id,
                     major_kind_name,
                     major_id,
                     major_name
                 from 
                     config_major
                 order by 
                     major_kind_id, major_id";

                    var configMajors = await sqlConnection.QueryAsync<config_major>(sql);

                    // 构建级联数据结构
                    var cascaderData = configMajors
                        .GroupBy(m => new { m.major_kind_id, m.major_kind_name })
                        .Select(group => new JobCascaderViewModel
                        {
                            value = group.Key.major_kind_id,
                            label = group.Key.major_kind_name,
                            children = group.Select(major => new JobCascaderViewModel
                            {
                                value = major.major_id,
                                label = major.major_name
                            }).ToList()
                        })
                        .ToList();

                    return cascaderData;
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine($"获取职位级联数据失败: {ex.Message}");
                return new List<JobCascaderViewModel>();
            }
        }
        public async Task<int> engage_resumeInsert(engage_resume resume)
        {
            using (SqlConnection con = new SqlConnection(connectionString))
            {
                await con.OpenAsync();

                // 构建参数化查询参数
                var parameters = new DynamicParameters();

                // 非空必填字段
                parameters.Add("@human_name", resume.human_name);
                parameters.Add("@engage_type", resume.engage_type);
                parameters.Add("@human_telephone", resume.human_telephone);
                parameters.Add("@human_idcard", resume.human_idcard);

                // 可空字段处理
                parameters.Add("@human_address", resume.human_address);
                parameters.Add("@human_postcode", resume.human_postcode);
                parameters.Add("@human_major_kind_id", resume.human_major_kind_id);
                parameters.Add("@human_major_kind_name", resume.human_major_kind_name);
                parameters.Add("@human_major_id", resume.human_major_id);
                parameters.Add("@human_major_name", resume.human_major_name);
                parameters.Add("@human_homephone", resume.human_homephone);
                parameters.Add("@human_mobilephone", resume.human_mobilephone);
                parameters.Add("@human_email", resume.human_email);
                parameters.Add("@human_hobby", resume.human_hobby);
                parameters.Add("@human_specility", resume.human_specility);
                parameters.Add("@human_sex", resume.human_sex);
                parameters.Add("@human_religion", resume.human_religion);
                parameters.Add("@human_party", resume.human_party);
                parameters.Add("@human_nationality", resume.human_nationality);
                parameters.Add("@human_race", resume.human_race);
                parameters.Add("@human_birthday", resume.human_birthday);
                parameters.Add("@human_age", resume.human_age);
                parameters.Add("@human_educated_degree", resume.human_educated_degree);
                parameters.Add("@human_educated_years", resume.human_educated_years);
                parameters.Add("@human_educated_major", resume.human_educated_major);
                parameters.Add("@human_college", resume.human_college);
                parameters.Add("@human_birthplace", resume.human_birthplace);
                parameters.Add("@demand_salary_standard", resume.demand_salary_standard);
                parameters.Add("@human_history_records", resume.human_history_records);
                parameters.Add("@remark", resume.remark);
                parameters.Add("@recomandation", resume.recomandation);
                parameters.Add("@human_picture", resume.human_picture);
                parameters.Add("@attachment_name", resume.attachment_name);
                parameters.Add("@check_status", resume.check_status);
                parameters.Add("@register", resume.register);
                parameters.Add("@regist_time", resume.regist_time);
                parameters.Add("@checker", resume.checker);
                parameters.Add("@check_time", resume.check_time);
                parameters.Add("@interview_status", resume.interview_status);
                parameters.Add("@total_points", resume.total_points);
                parameters.Add("@test_amount", resume.test_amount);
                parameters.Add("@test_checker", resume.test_checker);
                parameters.Add("@test_check_time", resume.test_check_time);
                parameters.Add("@pass_register", resume.pass_register);
                parameters.Add("@pass_regist_time", resume.pass_regist_time);
                parameters.Add("@pass_checker", resume.pass_checker);
                parameters.Add("@pass_check_time", resume.pass_check_time);
                parameters.Add("@pass_check_status", resume.pass_check_status);
                parameters.Add("@pass_checkComment", resume.pass_checkComment);
                parameters.Add("@pass_passComment", resume.pass_passComment);

                // 设置输出参数用于获取自增主键
                parameters.Add("@res_id", dbType: DbType.Int16, direction: ParameterDirection.Output);

                string sql = @"
            INSERT INTO engage_resume (
                human_name, engage_type, human_address, human_postcode, human_major_kind_id, 
                human_major_kind_name, human_major_id, human_major_name, human_telephone, 
                human_homephone, human_mobilephone, human_email, human_hobby, human_specility, 
                human_sex, human_religion, human_party, human_nationality, human_race, 
                human_birthday, human_age, human_educated_degree, human_educated_years, 
                human_educated_major, human_college, human_idcard, human_birthplace, 
                demand_salary_standard, human_history_records, remark, recomandation, 
                human_picture, attachment_name, check_status, register, regist_time, 
                checker, check_time, interview_status, total_points, test_amount, 
                test_checker, test_check_time, pass_register, pass_regist_time, 
                pass_checker, pass_check_time, pass_check_status, pass_checkComment, 
                pass_passComment
            ) VALUES (
                @human_name, @engage_type, @human_address, @human_postcode, @human_major_kind_id, 
                @human_major_kind_name, @human_major_id, @human_major_name, @human_telephone, 
                @human_homephone, @human_mobilephone, @human_email, @human_hobby, @human_specility, 
                @human_sex, @human_religion, @human_party, @human_nationality, @human_race, 
                @human_birthday, @human_age, @human_educated_degree, @human_educated_years, 
                @human_educated_major, @human_college, @human_idcard, @human_birthplace, 
                @demand_salary_standard, @human_history_records, @remark, @recomandation, 
                @human_picture, @attachment_name, @check_status, @register, @regist_time, 
                @checker, @check_time, @interview_status, @total_points, @test_amount, 
                @test_checker, @test_check_time, @pass_register, @pass_regist_time, 
                @pass_checker, @pass_check_time, @pass_check_status, @pass_checkComment, 
                @pass_passComment
            );
            SET @res_id = SCOPE_IDENTITY();";


                return await con.ExecuteAsync(sql, parameters);
            }
        }
        public async Task<int> major_kind_update(config_major_kind model)
        {
            using (SqlConnection con = new SqlConnection(connectionString))
            {
                var parameters = new DynamicParameters();
                parameters.Add("@major_kind_id", model.major_kind_id);
                parameters.Add("@major_kind_name", model.major_kind_name);
                parameters.Add("@mfk_id", model.mfk_id);

                string sql = @"
         UPDATE config_major_kind 
    SET 
        major_kind_id = @major_kind_id,
        major_kind_name = @major_kind_name
    WHERE 
        mfk_id = @mfk_id";

                return await con.ExecuteAsync(sql, parameters);
            }
        }
        public async Task<int> major_kind_add(config_major_kind model)
        {
            using (SqlConnection con = new SqlConnection(connectionString))
            {
                var parameters = new DynamicParameters();
                parameters.Add("@major_kind_id", model.major_kind_id);
                parameters.Add("@major_kind_name", model.major_kind_name);


                string sql = @"
        INSERT INTO config_major_kind(
            major_kind_id, 
            major_kind_name
            
        ) VALUES (
            @major_kind_id, 
            @major_kind_name
        );";

                return await con.ExecuteAsync(sql, parameters);
            }
        }
        public async Task<int> public_charinsert(config_public_char model)
        {
            using (SqlConnection con = new SqlConnection(connectionString))
            {
                var parameters = new DynamicParameters();
                parameters.Add("@attribute_kind", model.attribute_kind);
                parameters.Add("@attribute_name", model.attribute_name);
               

                string sql = @"
        INSERT INTO config_public_char(
            attribute_kind, 
            attribute_name
            
        ) VALUES (
            @attribute_kind, 
            @attribute_name
        );";

                return await con.ExecuteAsync(sql, parameters);
            }
        }
        public async Task<int> config_public_charDelete(int id)
        {

            using (SqlConnection con = new SqlConnection("server=.;database=HR_DB;uid=sa;pwd=123456"))
            {
                string sql = @$"DELETE FROM config_public_char WHERE pbc_id = {id}";
                return await con.ExecuteAsync(sql);
            }

        }
        public async Task<int> config_majorinsert(config_major model)
        {
            using (SqlConnection con = new SqlConnection(connectionString))
            {
                var parameters = new DynamicParameters();
                parameters.Add("@major_kind_id", model.major_kind_id);
                parameters.Add("@major_kind_name", model.major_kind_name);
                parameters.Add("@major_id", model.major_id);
                parameters.Add("@major_name", model.major_name);
                parameters.Add("@test_amount", model.test_amount);

                string sql = @"
        INSERT INTO config_major(
            major_kind_id, 
            major_kind_name, 
            major_id, 
            major_name, 
            test_amount
        ) VALUES (
            @major_kind_id, 
            @major_kind_name, 
            @major_id, 
            @major_name, 
            @test_amount
        );";

                return await con.ExecuteAsync(sql, parameters);
            }
        }
        public async Task<int> config_major_kindDelete(int id)
        {

            using (SqlConnection con = new SqlConnection("server=.;database=HR_DB;uid=sa;pwd=123456"))
            {
                string sql = @$"DELETE FROM config_major_kind WHERE mfk_id = {id}";
                return await con.ExecuteAsync(sql);
            }

        }
        public async Task<int> config_majorDelete(int id)
        {

            using (SqlConnection con = new SqlConnection("server=.;database=HR_DB;uid=sa;pwd=123456"))
            {
                string sql = @$"DELETE FROM config_major WHERE mak_id = {id}";
                return await con.ExecuteAsync(sql);
            }

        }
        public async Task<int> thirdupdate(config_file_third_kind dz)
        {
            using (SqlConnection con = new SqlConnection(connectionString))
            {
                var parameters = new DynamicParameters();
                parameters.Add("@ftk_id", dz.ftk_id); // 使用config_file_third_kind的主键
                parameters.Add("@first_kind_id", dz.first_kind_id);
                parameters.Add("@first_kind_name", dz.first_kind_name);
                parameters.Add("@second_kind_id", dz.second_kind_id);
                parameters.Add("@second_kind_name", dz.second_kind_name);
                parameters.Add("@third_kind_id", dz.third_kind_id);
                parameters.Add("@third_kind_name", dz.third_kind_name);
                parameters.Add("@third_kind_sale_id", dz.third_kind_sale_id);
                parameters.Add("@third_kind_is_retail", dz.third_kind_is_retail);

                // 更新config_file_third_kind表
                string sql = @"
    UPDATE config_file_third_kind 
    SET 
        first_kind_id = @first_kind_id,
        first_kind_name = @first_kind_name,
        second_kind_id = @second_kind_id,
        second_kind_name = @second_kind_name,
        third_kind_id = @third_kind_id,
        third_kind_name = @third_kind_name,
        third_kind_sale_id = @third_kind_sale_id,
        third_kind_is_retail = @third_kind_is_retail
    WHERE 
        ftk_id = @ftk_id";

                return await con.ExecuteAsync(sql, parameters);
            }
        }
        public async Task<int> thirdDelete(int id)
        {

            using (SqlConnection con = new SqlConnection("server=.;database=HR_DB;uid=sa;pwd=123456"))
            {
                string sql = @$"DELETE FROM config_file_third_kind WHERE ftk_id = {id}";
                return await con.ExecuteAsync(sql);
            }

        }
        public async Task<int> thirdInsert(config_file_third_kind model)
        {
            using (SqlConnection con = new SqlConnection(connectionString))
            {

                // 防止SQL注入：使用参数化查询（已通过DynamicParameters实现）
                var parameters = new DynamicParameters();
                //parameters.Add("@ftk_id", model.ftk_id, DbType.Int32, direction: ParameterDirection.Output); // 自增主键输出
                parameters.Add("@first_kind_id", model.first_kind_id);
                parameters.Add("@first_kind_name", model.first_kind_name);
                parameters.Add("@second_kind_id", model.second_kind_id);
                parameters.Add("@second_kind_name", model.second_kind_name);
                parameters.Add("@third_kind_id", model.third_kind_id);
                parameters.Add("@third_kind_name", model.third_kind_name);
                parameters.Add("@third_kind_sale_id", model.third_kind_sale_id);
                parameters.Add("@third_kind_is_retail", model.third_kind_is_retail); // 若为bool，需转换为1/0：model.third_kind_is_retail ? "1" : "0"

                string sql = @"
                INSERT INTO config_file_third_kind (
                    first_kind_id, 
                    first_kind_name, 
                    second_kind_id, 
                    second_kind_name, 
                    third_kind_id, 
                    third_kind_name, 
                    third_kind_sale_id, 
                    third_kind_is_retail
                ) VALUES (
                    @first_kind_id, 
                    @first_kind_name, 
                    @second_kind_id, 
                    @second_kind_name, 
                    @third_kind_id, 
                    @third_kind_name, 
                    @third_kind_sale_id, 
                    @third_kind_is_retail
                );";

                return await con.ExecuteAsync(sql, parameters);

            }
        }
        
            // 执行插入并获取自增主键
        public async Task<int> secondupdate(config_file_second_kind dz)
        {
            using (SqlConnection con = new SqlConnection(connectionString))
            {
                var parameters = new DynamicParameters();
                parameters.Add("@fsk_id", dz.fsk_id); // 主键参数名与前端一致
                parameters.Add("@first_kind_id", dz.first_kind_id); // I级机构编号（可能用于关联，但根据需求可能不需要作为更新条件）
                parameters.Add("@first_kind_name", dz.first_kind_name);
                parameters.Add("@second_kind_id", dz.second_kind_id); // II级机构编号
                parameters.Add("@second_kind_name", dz.second_kind_name); // II级机构名称（必填字段）
                parameters.Add("@second_salary_id", dz.second_salary_id); // 薪酬责任人编号（修正为second_）
                parameters.Add("@second_sale_id", dz.second_sale_id); // 销售责任人编号（修正为second_）

                // 假设表名为 config_file_second_kind（根据类名推测）
                string sql = @"
            UPDATE config_file_second_kind 
            SET 
                first_kind_id = @first_kind_id,
                first_kind_name = @first_kind_name,
                second_kind_id = @second_kind_id,
                second_kind_name = @second_kind_name, -- 更新II级机构名称
                second_salary_id = @second_salary_id, -- 更新薪酬责任人编号
                second_sale_id = @second_sale_id -- 更新销售责任人编号
            WHERE 
                fsk_id = @fsk_id"; // 使用正确的主键字段名

                return await con.ExecuteAsync(sql, parameters);
            }
        }
        public async Task<int> secondDelete(int id)
        {

            using (SqlConnection con = new SqlConnection("server=.;database=HR_DB;uid=sa;pwd=123456"))
            {
                string sql = @$"DELETE FROM config_file_second_kind WHERE fsk_id = {id}";
                return await con.ExecuteAsync(sql);
            }

        }
        public async Task<int> secondinsert(config_file_second_kind dz)
        {
            using (SqlConnection con = new SqlConnection(connectionString))
            {
                var parameters = new DynamicParameters();
                parameters.Add("@first_kind_id", dz.first_kind_id);
                parameters.Add("@first_kind_name", dz.first_kind_name);
                parameters.Add("@second_kind_id", dz.second_kind_id);
                parameters.Add("@second_kind_name", dz.second_kind_name);
                parameters.Add("@second_salary_id", dz.second_salary_id);
                parameters.Add("@second_sale_id", dz.second_sale_id);

                string sql = @"
           INSERT INTO config_file_second_kind (
                first_kind_id,
                first_kind_name,
                second_kind_id,
                second_kind_name,
                second_salary_id,
                second_sale_id
            ) VALUES (
                @first_kind_id,
                @first_kind_name,
                @second_kind_id,
                @second_kind_name,
                @second_salary_id,
                @second_sale_id
            );";

                return await con.ExecuteAsync(sql, parameters);
            }
        }
        //dbo.config_file_first_kind表

        public async Task<int> configupdate(config_file_first_kind dz)
        {
            using (SqlConnection con = new SqlConnection(connectionString))
            {
                var parameters = new DynamicParameters();
                parameters.Add("@ffk_id", dz.ffk_id);
                parameters.Add("@first_kind_id", dz.first_kind_id);  // 作为查询条件
                parameters.Add("@first_kind_name", dz.first_kind_name);
                parameters.Add("@first_kind_salary_id", dz.first_kind_salary_id);
                parameters.Add("@first_kind_sale_id", dz.first_kind_sale_id);

                string sql = @"
            UPDATE config_file_first_kind 
            SET 
                first_kind_name = @first_kind_name,
                first_kind_salary_id = @first_kind_salary_id,
                first_kind_sale_id = @first_kind_sale_id
            WHERE 
                ffk_id = @ffk_id";  // 根据ID更新

                return await con.ExecuteAsync(sql, parameters);
            }
        }
        public async Task<int> configDelete(int id)
        {

            using (SqlConnection con = new SqlConnection("server=.;database=HR_DB;uid=sa;pwd=123456"))
            {
                string sql = @$"DELETE FROM config_file_first_kind WHERE ffk_id = {id}";
                return await con.ExecuteAsync(sql);
            }

        }
        public async Task<int> configinsert(config_file_first_kind dz)
        {
            using (SqlConnection con = new SqlConnection(connectionString))
            {
                var parameters = new DynamicParameters();
                parameters.Add("@first_kind_id", dz.first_kind_id);
                parameters.Add("@first_kind_name", dz.first_kind_name);
                parameters.Add("@first_kind_salary_id", dz.first_kind_salary_id);
                parameters.Add("@first_kind_sale_id", dz.first_kind_sale_id);

                string sql = @"
            INSERT INTO config_file_first_kind (
                first_kind_id, first_kind_name, first_kind_salary_id, first_kind_sale_id
            ) VALUES (
                @first_kind_id, @first_kind_name, @first_kind_salary_id, @first_kind_sale_id
            )";

                return await con.ExecuteAsync(sql, parameters);
            }
        }
        public async Task<int> Login(string username, string password) // 参数名改为 username/password
        {
            
            string sql = "SELECT COUNT(*) FROM users WHERE u_true_name = @Username AND u_password = @Password"; // 使用 COUNT(*) 更高效

            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                await connection.OpenAsync();

                using (SqlCommand command = new SqlCommand(sql, connection))
                {
                    // 显式指定参数名（注意 SQL 中的参数名与 @ 符号匹配）
                    command.Parameters.AddWithValue("@Username", username); 
                    command.Parameters.AddWithValue("@Password", password); 

                    int count = (int)await command.ExecuteScalarAsync();
                    return count;
                }
            }
        }
        public async Task<int> update(engage_major_release dz)
        {
            using (SqlConnection con = new SqlConnection(connectionString))
            {
                var parameters = new DynamicParameters();
                parameters.Add("@mre_id", dz.mre_id); // 使用实际的主键
                parameters.Add("@first_kind_id", dz.first_kind_id);
                parameters.Add("@first_kind_name", dz.first_kind_name);
                parameters.Add("@second_kind_id", dz.second_kind_id);
                parameters.Add("@second_kind_name", dz.second_kind_name);
                parameters.Add("@third_kind_id", dz.third_kind_id);
                parameters.Add("@third_kind_name", dz.third_kind_name);
                parameters.Add("@major_kind_id", dz.major_kind_id);
                parameters.Add("@major_kind_name", dz.major_kind_name);
                parameters.Add("@major_id", dz.major_id);
                parameters.Add("@major_name", dz.major_name);
                parameters.Add("@human_amount", dz.human_amount);
                parameters.Add("@engage_type", dz.engage_type);
                parameters.Add("@deadline", dz.deadline);
                parameters.Add("@register", dz.register);
                parameters.Add("@changer", dz.changer);
                parameters.Add("@regist_time", dz.regist_time);
                parameters.Add("@change_time", dz.change_time);
                parameters.Add("@major_describe", dz.major_describe);
                parameters.Add("@engage_required", dz.engage_required);
                parameters.Add("@name", dz.name);

                string sql = @"
                UPDATE engage_major_release
                SET 
                    first_kind_id = @first_kind_id,
                    first_kind_name = @first_kind_name,
                    second_kind_id = @second_kind_id,
                    second_kind_name = @second_kind_name,
                    
                    third_kind_id = @third_kind_id,
                    third_kind_name = @third_kind_name,
                    
                    human_amount = @human_amount,
                    engage_type = @engage_type,
                    deadline = @deadline,
                    register = @register,
                    changer = @changer,
                    regist_time = @regist_time,
                    change_time = @change_time,
                    major_describe = @major_describe,
                    engage_required = @engage_required,
                    name = @name
                WHERE 
                    mre_id = @mre_id";

                return await con.ExecuteAsync(sql, parameters);
            }
        }
        //public async Task<int> update(engage_major_release dz)
        //{
        //    using (SqlConnection con = new SqlConnection(connectionString))
        //    {
        //        string sql = @"
        //UPDATE engage_major_release 
        //SET first_kind_id = @first_kind_id, 
        //    first_kind_name = @first_kind_name, 
        //    second_kind_id = @second_kind_id, 
        //    second_kind_name = @second_kind_name, 
        //    third_kind_id = @third_kind_id, 
        //    third_kind_name = @third_kind_name, 
        //    major_kind_id = @major_kind_id, 
        //    major_kind_name = @major_kind_name, 
        //    major_id = @major_id, 
        //    major_name = @major_name, 
        //    human_amount = @human_amount, 
        //    engage_type = @engage_type,
        //    deadline = @deadline, 
        //    register = @register,
        //    changer = @changer, 
        //    regist_time = @regist_time, 
        //    change_time = @change_time, 
        //    major_describe = @major_describe, 
        //    engage_required = @engage_required,
        //    name = @name
        //WHERE major_id = @major_id";  // 假设使用major_id作为更新条件

        //        return await con.ExecuteAsync(sql, new
        //        {
        //            dz.first_kind_id,
        //            dz.first_kind_name,
        //            dz.second_kind_id,
        //            dz.second_kind_name,
        //            dz.third_kind_id,
        //            dz.third_kind_name,
        //            dz.major_kind_id,
        //            dz.major_kind_name,
        //            dz.major_id,
        //            dz.major_name,
        //            dz.human_amount,
        //            dz.engage_type,
        //            dz.deadline,
        //            dz.register,
        //            dz.changer,
        //            dz.regist_time,
        //            dz.change_time,
        //            dz.major_describe,
        //            dz.engage_required,
        //            dz.name
        //        });
        //    }
        //}

        public async Task<int> insert(engage_major_release dz)
        {
            using (SqlConnection con = new SqlConnection(connectionString))
            {
                string sql = @"
            INSERT INTO engage_major_release 
            VALUES (@first_kind_id, @first_kind_name, @second_kind_id, @second_kind_name, 
                   @third_kind_id, @third_kind_name, @major_kind_id, @major_kind_name, 
                   @major_id, @major_name, @human_amount, @engage_type,@deadline, @register,
                   @changer, @regist_time, @change_time, @major_describe, @engage_required,@name)";

                return await con.ExecuteAsync(sql, new
                {
                    dz.first_kind_id,
                    dz.first_kind_name,
                    dz.second_kind_id,
                    dz.second_kind_name,
                    dz.third_kind_id,
                    dz.third_kind_name,
                    dz.major_kind_id,
                    dz.major_kind_name,
                    dz.major_id,
                    dz.major_name,
                    dz.human_amount,
                    dz.engage_type,
                    dz.deadline,
                    dz.register,
                    dz.changer,
                    dz.regist_time,
                    dz.change_time,
                    dz.major_describe,
                    dz.engage_required,
                    dz.name
            
            
        });
            }
        }
        public async Task<int> Delete(int id)
        {

            using (SqlConnection con = new SqlConnection("server=.;database=HR_DB;uid=sa;pwd=123456"))
            {
                string sql = @$"DELETE FROM engage_major_release WHERE mre_id = {id}";
                return await con.ExecuteAsync(sql);
            }

        }

    }
}
